﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using VOL;
using DAL;

namespace BLL
{
    public class QuanHuyenBL
    {
        private DataAccess dataAccess;
        public QuanHuyenBL()
        {
            dataAccess = new DataAccess();
        }

        public DataTable GetData()
        {
            string strSql = "SELECT * FROM tbl_QuanHuyen ORDER BY MaQuanHuyen DESC ";
            return dataAccess.GetData(strSql);
        }
        public DataTable GetDataTP()
        {
            string strSql = "SELECT tbl_QuanHuyen.TenQuanHuyen, tbl_QuanHuyen.MaQuanHuyen, tbl_ThanhPho.TenThanhPho, tbl_QuanHuyen.MaThanhPho FROM tbl_QuanHuyen INNER JOIN tbl_ThanhPho ON tbl_QuanHuyen.MaThanhPho = tbl_ThanhPho.MaThanhPho ORDER BY MaQuanHuyen DESC";
            return dataAccess.GetData(strSql);
        }
        public DataTable GetDataTP(int MaThanhPho)
        {
            string strSql = "SELECT tbl_QuanHuyen.TenQuanHuyen, tbl_QuanHuyen.MaQuanHuyen, tbl_ThanhPho.TenThanhPho, tbl_QuanHuyen.MaThanhPho FROM tbl_QuanHuyen INNER JOIN tbl_ThanhPho ON tbl_QuanHuyen.MaThanhPho = tbl_ThanhPho.MaThanhPho WHERE tbl_ThanhPho.MaThanhPho = "+MaThanhPho.ToString()+" ORDER BY MaQuanHuyen DESC";
            return dataAccess.GetData(strSql);
        }
        //Thêm
        public bool insertQuanHuyen(QuanHuyen obj)
        {
            SqlParameter[] param = new SqlParameter[2];
            param[0] = new SqlParameter("@TenQuanHuyen", SqlDbType.NVarChar) { Value = obj.TenQuanHuyen };
            param[1] = new SqlParameter("@MaThanhPho", SqlDbType.Int) { Value = obj.MaThanhPho };

            string strSql = "INSERT INTO tbl_QuanHuyen(TenQuanHuyen,MaThanhPho) VALUES (@TenQuanHuyen,@MaThanhPho)";
            dataAccess.Execute(strSql, param);

            return true;
        }

        ////Xóa
        public bool deleteQuanHuyen(QuanHuyen obj)
        {
            SqlParameter[] param = new SqlParameter[1];
            param[0] = new SqlParameter("@MaQuanHuyen", SqlDbType.Int) { Value = obj.MaQuanHuyen };

            string strSql = "DELETE FROM tbl_QuanHuyen WHERE MaQuanHuyen = @MaQuanHuyen";
            dataAccess.Execute(strSql, param);
            return true;
        }

        ////Sửa
        public bool updateQuanHuyen(QuanHuyen obj)
        {
            SqlParameter[] param = new SqlParameter[3];
            param[0] = new SqlParameter("@TenQuanHuyen", SqlDbType.NVarChar) { Value = obj.TenQuanHuyen };
            param[1] = new SqlParameter("@MaQuanHuyen", SqlDbType.Int) { Value = obj.MaQuanHuyen };
            param[2] = new SqlParameter("@MaThanhPho", SqlDbType.Int) { Value = obj.MaThanhPho };
            string strSql = "UPDATE tbl_QuanHuyen SET " +
                            "TenQuanHuyen = @TenQuanHuyen, " +
                            "MaThanhPho = @MaThanhPho " +
                            "WHERE MaQuanHuyen = @MaQuanHuyen";
            dataAccess.Execute(strSql, param);
            return true;
        }

        ////Tìm theo Mã Quận Huyện
        public DataTable searchIDQuanHuyen(int maQuanHuyen)
        {
            SqlParameter[] param = new SqlParameter[1];
            param[0] = new SqlParameter("@MaQuanHuyen", SqlDbType.Int) { Value = maQuanHuyen };

            string strSql = "SELECT * FROM tbl_QuanHuyen WHERE MaQuanHuyen = @MaQuanHuyen";
            return dataAccess.GetData(strSql, param);
        }
    }
}
